package com.wang.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * 思路: 
 * 	~码一个标准化的jdbc;(standardJdbc) 
 * 	~封装jdbc: 加载驱动 获取链接 关闭链接
 * 
 * 	~ 连接池;
 * 
 * @author Zjx
 */
public class DbUtil
{
	/**
	 * 驱动
	 */
	private static String driver = ConstatFinalUtil.SYS_BUNDLE.getString("jdbc.driver").trim();
	/**
	 * url
	 */
	private static String url = ConstatFinalUtil.SYS_BUNDLE.getString("jdbc.url").trim();
	/**
	 * 用户名
	 */
	private static String user = ConstatFinalUtil.SYS_BUNDLE.getString("jdbc.user").trim();
	/**
	 * 密码
	 */
	private static String password = ConstatFinalUtil.SYS_BUNDLE.getString("jdbc.password").trim();
	
	/**
	 * 连接的容器
	 */
	private List<Connection> connectionList = new ArrayList<Connection>();
	/**
	 * 初始连接池的大小
	 */
	private int initSize = Integer.valueOf(ConstatFinalUtil.SYS_BUNDLE.getString("db.pool.init.size"));
	
	/**
	 * 自身的属性
	 */
	private static DbUtil DB_UTIL = new DbUtil();
	
	/**
	 * 私有的,外面就不能调用了
	 */
	private DbUtil()
	{
		this.initBatch();
	}
	
	/**
	 * 批量初始化
	 */
	public void initBatch()
	{
		/* 初始化连接池 */
		for(int i = 0 ; i < initSize ; i ++ )
		{
			Connection connection = this.initConnection();
			if(connection != null)
			{
				/* 只加载成功的链接 */
				connectionList.add(connection);
			}
		}
	}

	public static void main(String[] args)
	{
		//standardJdbc();
		newjdbc();
		//newjdbc();
//		for(int i = 0 ; i < 100 ; i ++)
//		{
//			System.out.println(i + "====" + DbUtil.getInstance());
//		}
	}

	/**
	 * 提供一个静态的方法,返回值是自己
	 * @return
	 */
	public static DbUtil getInstance()
	{
		return DB_UTIL ; 
	}
	
	/**
	 * 新版本的jdbc
	 */
	private static void newjdbc()
	{
		DbUtil dbUtil = getInstance() ; 

		String sql = "select * from a_admins";
		/* 模板字符串 */
		String formatStr = "id:%s,email:%s,status:%s,createTime:%s,updateTime:%s";

		Connection connection = dbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try
		{
			/* 操作数据库 */
			ps = connection.prepareStatement(sql);
			/* 为占位符赋值 */
			/*
			 * cud:更新操作 r:查询
			 */
			rs = ps.executeQuery();
			/*
			 * 如果是单条使用:if 如果是多条使用while
			 */
			while (rs.next())
			{
				/* 获取值 */
				int id = rs.getInt("id");
				String email = rs.getString("email");
				byte status = rs.getByte("status");
				Date createTime = rs.getTimestamp("createTime");
				Date updateTime = rs.getTimestamp("updateTime");

				String result = String.format(formatStr, id, email, status, createTime.toLocaleString(),
						updateTime.toLocaleString());
				System.out.println(result);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} catch (Exception e)
		{
			e.printStackTrace();
		} finally
		{
			dbUtil.closeAll(connection, ps, rs);
		}
	}

	/**
	 * 静态代码块,只执行一次,加载类的时候执行,不会多次执行,不需要new对象
	 */
	static
	{
		try
		{
			/* 加载驱动 */
			Class.forName(driver);
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}
	}

	/**
	 * 初始化链接
	 * 
	 * @return
	 */
	private Connection initConnection()
	{
		try
		{
			/* 获取链接 */
			Connection connection = DriverManager.getConnection(url, user, password);
			return connection;
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 获取链接
	 * @return
	 */
	public Connection getConnection()
	{
		if(this.connectionList.size() == 0 )
		{
			this.initBatch();  
		}
		/* 从容器中获取一个连接线(随机) */
		Random rand = new Random();
		/* 获取一个随机数 */
		int randInt = rand.nextInt(connectionList.size());
		/* 随机获取一个链接 */
		Connection connection =  connectionList.get(randInt);
		/* 为了不让多个人同时访问一个链接,移出去 */
		connectionList.remove(randInt);
		//System.out.println("连接池大小:"+ connectionList.size() +";使用的是下标值:" + randInt + "===connection:" + connection);
		return connection ; 
	}

	/**
	 * 关闭资源
	 */
	public void closeAll(Connection connection, Statement ps, ResultSet rs)
	{
		/* 把这个链接再放回去 */
		connectionList.add(connection);
		
		/* 关闭链接 */
		try
		{
			if (rs != null)
			{
				rs.close();
				rs = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}

		try
		{
			if (ps != null)
			{
				ps.close();
				ps = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}

//		try
//		{
//			if (connection != null)
//			{
//				connection.close();
//				connection = null;
//			}
//		} catch (SQLException e)
//		{
//			e.printStackTrace();
//		}
	}

	/**
	 * 标准的jdbc
	 */
	private static void standardJdbc()
	{
		/* 四步走 */
		String driver = "com.mysql.cj.jdbc.Driver";
		String url = "jdbc:mysql:///userscenter_test?serverTimezone=GMT%2b8";
		String user = "root";
		String password = "123456";
		String sql = "select * from a_role";

		/* 模板字符串 */
		String formatStr = "id:%s,name:%s,content:%s,status:%s,createTime:%s,updateTime:%s,pubTime:%s";

		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try
		{
			/* 加载驱动 */
			Class.forName(driver);
			/* 获取链接 */
			connection = DriverManager.getConnection(url, user, password);
			/* 操作数据库 */
			ps = connection.prepareStatement(sql);
			/* 为占位符赋值 */
			/*
			 * cud:更新操作 r:查询
			 */
			rs = ps.executeQuery();
			/*
			 * 如果是单条使用:if 如果是多条使用while
			 */
			while (rs.next())
			{
				/* 获取值 */
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String content = rs.getString("content");
				byte status = rs.getByte("status");
				Date createTime = rs.getTimestamp("createTime");
				Date updateTime = rs.getTimestamp("updateTime");
				Date pubTime = rs.getTimestamp("pubTime");

				String result = String.format(formatStr, id, name, content, status, createTime.toLocaleString(),
						updateTime.toLocaleString(), pubTime.toLocaleString());
				System.out.println(result);
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} catch (Exception e)
		{
			e.printStackTrace();
		} finally
		{
			/* 关闭链接 */
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}

			try
			{
				if (ps != null)
				{
					ps.close();
					ps = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}

			try
			{
				if (connection != null)
				{
					connection.close();
					connection = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
}
